﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace CreatePGSQLDataScript
{
    public partial class DBAnalyse : Form
    {
        public DBAnalyse()
        {
            InitializeComponent();
        }
        DataAccess.PgSqlProvider db1;
        DataAccess.SqlProvider db2;
        DataAccess.SqlProvider db3;
        DataTable dttable = new DataTable();
        string dbName = "con2010";
        private void DBAnalyse_Load(object sender, EventArgs e)
        {
            historyList.ReadList(cmbConn1);
            db2 = new DataAccess.SqlProvider(OisLibrary.DB.Connection);
            dbName = txtdbname.Text;
            txtdbname.Text = dbName;
            db2.Open();
        }
        private void btnConn_Click(object sender, EventArgs e)
        {
            db1 = new DataAccess.PgSqlProvider(cmbConn1.Text);
            try
            {
                db1.Open();
                historyList.SaveValue(cmbConn1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


            
            txtdbname.Text = dbName;
            string sqlText = string.Format(@"select table_name,column_name,d.description   from  information_schema.columns as a
                                            inner join pg_class as c on a.table_name=c.relname and a.ordinal_position>=1 and a.table_schema='public'
                                            left join pg_description as d on d.objoid=c.oid and d.objsubid=a.ordinal_position
                                            where a.table_catalog='{0}' and a.table_schema='public' and  c.relkind='r' order by table_name,column_name", dbName);

            DataTable dt = db1.GetDataTable(sqlText);

            RefreshDB(dt,dbName);
            LoadTable(dbName);

        }

        private void btnConnMSSql_Click(object sender, EventArgs e)
        {
            dbName = txtdbname.Text;

            db3 = new DataAccess.SqlProvider(cmbConn1.Text);
            try
            {
                db3.Open();
                historyList.SaveValue(cmbConn1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }



            txtdbname.Text = dbName;
            string sqlText = @"select obj.name  table_name,c.name as column_name,g.value as description
from sysobjects obj 
inner join  syscolumns c on obj.id=c.id and obj.type='u'
left join sys.extended_properties g ON g.major_id = c.id AND c.colid = g.minor_id order by obj.name,c.name";

            DataTable dt = db3.GetDataTable(sqlText);

            RefreshDB(dt, dbName);
            LoadTable(dbName);
        }


        private void RefreshDB(DataTable dt, string dbname)
        {


            //删除
            StringBuilder sbColl=new StringBuilder();
            string sqlText=string.Format(@"
                         delete tbfield where dbname='{0}';
                         delete tbtable where dbname='{0}';
                         delete tbdb where dbname='{0}';",dbname);


            sbColl.Append(sqlText);

            //插入新记录
            sqlText =string.Format("insert into tbdb(dbname,dbdesc) values('{0}','');\n",dbname); 
            sbColl.Append(sqlText);
            string lasttableName="", tableName="",fieldName="",fielddesc="";
            
            for(int i=0;i<dt.Rows.Count ;i++)
            {
                DataRow dr = dt.Rows[i];
                tableName= dr["table_name"].ToString();
                fieldName= dr["column_name"].ToString();
                fielddesc=dr["description"].ToString();
                if(tableName!=lasttableName)
                {
                    //插入表
                    sqlText =string.Format( "insert into tbtable(dbname,tablename,tabledesc) values('{0}','{1}','{2}');\n",dbname,tableName,"");
                    lasttableName = tableName;
                    sbColl.Append(sqlText);
                }
                sqlText=string.Format(" insert into tbfield(dbname,tablename,fieldname ,fielddesc) values('{0}','{1}','{2}','{3}');\n",dbname,tableName,fieldName,fielddesc);
                sbColl.Append(sqlText);

                //if (i % 100 == 0)
                //{
                //    db2.ExecuteSQL(sbColl.ToString());
                //    sbColl = new StringBuilder();
                //}


            }

            db2.ExecuteSQL(sbColl.ToString());

            MessageBox.Show("刷新完成！");
 
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }

        private void LoadTable(string dbname)
        {
            string sqlText = string.Format(@"select t1.tablename,t1.tablename+'('+ISNULL(t2.tabledesc, t1.tabledesc) +')' as tabledesc from tbtable as t1
                                                left join tbtablecustom as t2 on t1.dbname=t2.dbname and t1.tablename =t2.tablename
                                                 where t1.dbname='{0}'", dbname);
            dttable = db2.GetDataTable(sqlText);
            
            lboxtable.ValueMember = "tablename";
            lboxtable.DisplayMember = "tabledesc";
            lboxtable.DataSource = dttable;
        }

        private void txttablename_TextChanged(object sender, EventArgs e)
        {
            string tablename = txttablename.Text;
            dttable.DefaultView.RowFilter = string.Format("tablename like '%{0}%' ", tablename);

        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            LoadTable(txtdbname.Text.Trim());
        }

        private void lboxtable_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            int index = lboxtable.SelectedIndex;
            DataRowView drv1 = (DataRowView)lboxtable.Items[index];
            string tableName = drv1["tablename"].ToString();

            string sqlText = string.Format("select tabledesc from tbtablecustom   where dbname='{0}' and tablename='{1}' ", dbName, tableName);
            object obj = db2.ExecuteScalar(sqlText);
            if (obj != null)
            {
                txttbDesc.Text = obj.ToString();
            }
            else
            {
                txttbDesc.Text = "";
            }
            txtFieldDesc.Text = "";
            chkImportant.Checked = false;
            LoadColumns(tableName);

        }

        private void LoadColumns(string tableName)
        {
            string sqlText = string.Format(@"select t1.fieldname,t1.fielddesc as desc1,isnull(t2.fielddesc,'') as desc2 ,isnull(t2.isimportant,'') as isimportant
                                                from tbfield as t1
                                                left join tbfieldcustom as t2 on t2.dbname=t1.dbname and t2.tablename=t1.tablename 
                                                and t2.fieldname =t1.fieldname
                                                where t1.dbname ='{0}' and t1.tablename ='{1}'", dbName, tableName);
            DataTable dt= db2.GetDataTable(sqlText);
            gridColumns.DataSource = dt;
        }

        private void gridColumns_DoubleClick(object sender, EventArgs e)
        {
            DataGridViewRow dr = gridColumns.CurrentRow;
            string value= dr.Cells[2].Value.ToString();
            txtFieldDesc.Text = value;

            chkImportant.Checked = (bool)dr.Cells[3].Value;
        }

        private void btnSaveTable_Click(object sender, EventArgs e)
        {
            string dbname = getCurrDbName();
            string tablename = getCurrTableName();
            string tableDesc=txttbDesc.Text.Trim();
            string sqlText = string.Format(@"
                    if exists(select 1 from tbtablecustom where dbname='{0}' and tablename='{1}')
                    begin
	                    update tbtablecustom set tabledesc='{2}' where dbname='{0}' and tablename='{1}';
                    end
                    else
                    begin
	                    insert into tbtablecustom values('{0}','{1}','{2}');
                    end", dbname, tablename, tableDesc);
            db2.ExecuteSQL(sqlText);
        }

        private void btnSaveField_Click(object sender, EventArgs e)
        {
            string dbname = getCurrDbName();
            string tablename = getCurrTableName();
            string fieldname=getCurrFieldName();
            string fieldDesc=txtFieldDesc.Text.Trim();
            string important = chkImportant.Checked ? "True" : "False";


            string sqlText = string.Format(@"
                        if exists(select 1 from tbfieldcustom where dbname='{0}' and tablename='{1}' and fieldname='{2}')
                        begin
	                        update tbfieldcustom set fielddesc='{3}',isimportant='{4}' where dbname='{0}' and tablename='{1}' and fieldname='{2}';
                        end
                        else
                        begin
	                        insert into tbfieldcustom values('{0}','{1}','{2}','{3}','{4}');
                        end", dbname, tablename,fieldname, fieldDesc,important);
            db2.ExecuteSQL(sqlText);

        }


        private string getCurrDbName()
        {
            return txtdbname.Text.Trim();
        }
        private string getCurrTableName()
        {
            int index = lboxtable.SelectedIndex;
            DataRowView drv1 = (DataRowView)lboxtable.Items[index];
            string tableName = drv1["tablename"].ToString();
            return tableName;
        }
        private string getCurrFieldName()
        {
            DataGridViewRow dr = gridColumns.CurrentRow;
            string value = dr.Cells[0].Value.ToString();
            return value;
        }



    }
}
